package com.library.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.library.bean.Book;
import com.library.dao.BaseDao;
import com.library.dao.BookDao;

public class BookDaoImpl implements BookDao {

	@Override
	public List<Book> selectBookAll(int pageNum ,int pageSize ,String bookId , String bookNo,String bookName,String bookWriter,double minBookPrice,double maxBookPrice,String bookType) {
		List<Book> list = new ArrayList<Book>();
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM BOOK WHERE (BOOKID LIKE ? AND BOOKNO LIKE ?"
				+ " AND BOOKNAME LIKE ? AND BOOKWRITER LIKE ? AND BOOKTYPE LIKE ?) AND BOOKPRICE >= ?"
				+ " AND BOOKPRICE <= ? LIMIT ?,?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, "%"+bookId+"%");
			ps.setString(2, "%"+bookNo+"%");
			ps.setString(3, "%"+bookName+"%");
			ps.setString(4, "%"+bookWriter+"%");
			ps.setString(5, "%"+bookType+"%");
			ps.setDouble(6, minBookPrice);
			ps.setDouble(7, maxBookPrice);
			ps.setInt(8, (pageNum-1)*pageSize);
			ps.setInt(9, pageSize);
			rs = ps.executeQuery();
			while(rs.next()) {
				Book book = new Book();
				book.setBookId(rs.getInt("bookId"));
				book.setBookNo(rs.getString("bookNo"));
				book.setBookName(rs.getString("bookName"));
				book.setBookWriter(rs.getString("bookWriter"));
				book.setBookPrice(rs.getDouble("bookPrice"));
				book.setBookNum(rs.getInt("bookNum"));
				book.setBookType(rs.getString("bookType"));
				list.add(book);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			BaseDao.closeAll(rs, ps, con);
		}
		return list;
	}

	@Override
	public int bookTotalRows(String bookId , String bookNo,String bookName,String bookWriter,double minBookPrice,double maxBookPrice,String bookType) {
		int num = 0;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "SELECT COUNT(1) AS ROWS FROM BOOK WHERE (BOOKID LIKE ? AND BOOKNO LIKE ?" 
				+ " AND BOOKNAME LIKE ? AND BOOKWRITER LIKE ? AND BOOKTYPE LIKE ?) AND BOOKPRICE >= ?" 
				+ " AND BOOKPRICE <= ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, "%"+bookId+"%");
			ps.setString(2, "%"+bookNo+"%");
			ps.setString(3, "%"+bookName+"%");
			ps.setString(4, "%"+bookWriter+"%");
			ps.setString(5, "%"+bookType+"%");
			ps.setDouble(6, minBookPrice);
			ps.setDouble(7, maxBookPrice);
			rs = ps.executeQuery();
			if(rs.next()) {
				num = rs.getInt("ROWS");
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			BaseDao.closeAll(rs, ps, con);
		}
		return num;
	}

	@Override
	public int deleteBookByBookId(int bookId) {
		int num = 0;
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "DELETE FROM BOOK WHERE BOOKID = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, bookId);
			num = ps.executeUpdate();
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			BaseDao.closeAll(rs, ps, con);
		}
		return num;
	}
}
